﻿using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;

namespace PSI.Services.TMS {
    public class OutPlanReportService : PSIBaseService {
        public JArray GenReportCurrentMonth() {
            var maxday = DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
            var year = DateTime.Now.Year.ToString();
            var month = DateTime.Now.Month.ToString();
            var day = DateTime.Now.Day+1;
            var listDate = new List<string>();
            for (int i = day; i > 0; i--) {
                listDate.Add(year + "-" + month + "-" + i.ToString());
            }

            var sql = @"select distinct f_address from tms_out_consignation_detail
                        where f_plan_act_dt between @dt1 and @dt2 and f_status is not null
                        order by f_address
                ";
            var p = CreateDbParameters();
            p.AddWithValue("dt1", listDate[listDate.Count - 1]);
          
            if (day > maxday)
            {
                var dts=(year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                p.AddWithValue("dt2", dts);
            }
            else
            {
                p.AddWithValue("dt2", listDate[0]);
            }
            var listAddress = new List<string>();
            Query(sql, p, reader => {
                listAddress.Add(reader.GetString(0));
            });

            var result = new JArray();

            foreach (var address in listAddress) {
                var r = new JObject();
                r.Add("address", address);
                var index = 0;
                foreach (var dt in listDate) {
                    // 计划数
                    sql = @"select count(*) 
                            from tms_out_consignation_detail
                            where f_plan_act_dt = @dt and f_address = @address
                            ";
                    p = CreateDbParameters();
                    var dyd = dt.Split('-');
                    if (int.Parse(dyd[2]) > maxday)
                    {
                        var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                        p.AddWithValue("dt", dts);
                    }
                    else
                    {
                        p.AddWithValue("dt", dt);
                    }
                    p.AddWithValue("address", address);
                    var cnt1 = QueryCount(sql, p);

                    if (cnt1 == 0) {
                        r.Add("f_" + index.ToString() + "_1", null);

                    } else {
                        r.Add("f_" + index.ToString() + "_1", cnt1);
                    }
                    //完成数
                    sql = @"select count(*) 
                            from tms_out_consignation_detail
                            where f_plan_act_dt = @dt and f_address = @address and f_status = '运输完成'
                            ";
                    p = CreateDbParameters();
                    if (int.Parse(dyd[2]) > maxday)
                    {
                        var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                        p.AddWithValue("dt", dts);
                    }
                    else
                    {
                        p.AddWithValue("dt", dt);
                    }
                    p.AddWithValue("address", address);
                    var cnt2 = QueryCount(sql, p);
                    if (cnt2 == 0) {
                        r.Add("f_" + index.ToString() + "_2", null);
                    } else {
                        r.Add("f_" + index.ToString() + "_2", cnt2);
                    }
                    index++;
                }

                // 小计
                // 计划数
                sql = @"select count(*) 
                            from tms_out_consignation_detail
                            where (f_plan_act_dt between @dt1 and @dt2) 
                                and (f_address = @address)
                            ";
                p = CreateDbParameters();
                p.AddWithValue("address", address);
                p.AddWithValue("dt1", listDate[listDate.Count - 1]);
                if (day > maxday)
                {
                    var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                    p.AddWithValue("dt2", dts);
                }
                else
                {
                    p.AddWithValue("dt2", listDate[0]);
                }

                var sumCnt1 = QueryCount(sql, p);

                r.Add("sum1", sumCnt1);

                // 完成数
                sql = @"select count(*) 
                            from tms_out_consignation_detail
                            where (f_plan_act_dt between @dt1 and @dt2) 
                                and (f_address = @address) and (f_status = '运输完成')
                            ";
                p = CreateDbParameters();
                p.AddWithValue("address", address);
                p.AddWithValue("dt1", listDate[listDate.Count - 1]);
                if (day > maxday)
                {
                    var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                    p.AddWithValue("dt2", dts);
                }
                else
                {
                    p.AddWithValue("dt2", listDate[0]);
                }
                var sumCnt2 = QueryCount(sql, p);

                r.Add("sum2", sumCnt2);

                result.Add(r);
            }

            // 合计
            var item = new JObject();
            item.Add("address", "本月合计");
            // 计划数
            sql = @"select count(*)
                    from tms_out_consignation_detail
                    where (f_plan_act_dt between @dt1 and @dt2) 
                    ";
            p = CreateDbParameters();
            p.AddWithValue("dt1", listDate[listDate.Count - 1]);
            if (day > maxday)
            {
                var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                p.AddWithValue("dt2", dts);
            }
            else
            {
                p.AddWithValue("dt2", listDate[0]);
            }

            item.Add("sum1", QueryCount(sql, p));

            // 完成数
            sql = @"select count(*) 
                    from tms_out_consignation_detail
                    where (f_plan_act_dt between @dt1 and @dt2) 
                           and (f_status = '运输完成')
                    ";
            p = CreateDbParameters();
            p.AddWithValue("dt1", listDate[listDate.Count - 1]);
            if (day > maxday)
            {
                var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                p.AddWithValue("dt2", dts);
            }
            else
            {
                p.AddWithValue("dt2", listDate[0]);
            }

            item.Add("sum2", QueryCount(sql, p));

            {
                var index = 0;
                foreach (var dt in listDate) {
                    // 计划数

                    sql = @"select count(*) 
                            from tms_out_consignation_detail
                            where f_plan_act_dt = @dt
                            ";
                    p = CreateDbParameters();
                    var dyd = dt.Split('-');
                    if (int.Parse(dyd[2]) > maxday)
                    {
                        var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                        p.AddWithValue("dt", dts);
                    }
                    else
                    {
                        p.AddWithValue("dt", dt);
                    }
                    var cnt1 = QueryCount(sql, p);
                    if (cnt1 == 0) {
                        item.Add("f_" + index.ToString() + "_1", null);

                    } else {
                        item.Add("f_" + index.ToString() + "_1", cnt1);
                    }
                    //完成数
                    sql = @"select count(*) 
                            from tms_out_consignation_detail
                            where f_plan_act_dt = @dt and f_status = '运输完成'
                            ";
                    p = CreateDbParameters();
                    if (int.Parse(dyd[2]) > maxday)
                    {
                        var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                        p.AddWithValue("dt", dts);
                    }
                    else
                    {
                        p.AddWithValue("dt", dt);
                    }
                    var cnt2 = QueryCount(sql, p);
                    if (cnt2 == 0) {
                        item.Add("f_" + index.ToString() + "_2", null);
                    } else {
                        item.Add("f_" + index.ToString() + "_2", cnt2);
                    }
                    index++;
                }
            }

            result.Insert(0, item);

            return result;
        }


        /// <summary>
        /// 出口计划汇总表
        /// </summary>
        /// <returns></returns>
        public JArray GetJKJHHZB(string dt)
        {
            var result = new JArray();
            try
            {

                var sql = new StringBuilder("select f_address,f_cnt_owner,f_goods,");
                sql.Append("(select COUNT(*) from [tms_out_consignation_detail] where (f_cnt_type_2 like '%20%' or f_cnt_type_1 like '%20%') and f_address=a.f_address and f_cnt_owner=a.f_cnt_owner ) '20',");
                sql.Append("(select COUNT(*) from [tms_out_consignation_detail] where (f_cnt_type_2 like '%40%' or f_cnt_type_1 like '%40%') and f_address=a.f_address and f_cnt_owner=a.f_cnt_owner ) '40',");
                sql.Append("f_srv_memo");
                sql.Append(" from [tms_out_consignation_detail] a");
                sql.Append(" where f_plan_act_dt=@dt group by f_address,f_cnt_owner,f_srv_memo,f_goods");

                var p = CreateDbParameters();

                p.AddWithValue("dt", dt);

                var h20 = 0;
                var h40 = 0;


                Query(sql.ToString(), p, ready =>
                {
                    var item = new JObject();
                    item.Add("Address", ready.GetString(0));
                    item.Add("CntOwner", ready.GetString(1));
                    item.Add("Good", ready.GetString(2));
                    item.Add("CntType1", ready.GetInt32(3));
                    item.Add("CntType2", ready.GetInt32(4));
                
                    item.Add("Memo", ready.GetString(5));
                   
                    h20 += ready.GetInt32(2);
                    h40 += ready.GetInt32(3);
                    result.Add(item);
                });

                var lastitem = new JObject();
                lastitem.Add("Address", "合计");
                lastitem.Add("CntType1", h20);
                lastitem.Add("CntType2", h40);

                result.Add(lastitem);
                return result;
            }
            catch (Exception ex)
            {
                return result;
            }
        }
    }
}